/**
 * Copyright  (c) 2009-2010 Wilmar IT Service,  Inc.   
 * All  rights  reserved. 
 */
package com.wilmar.process.procedure;

import java.sql.Array;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.wilmar.process.core.ProcessSystemDeclareKeys;
import com.wilmar.process.view.properties.IViewHistoryProcessInstanceProperties;

/**
 * @author MaKuBEX Email:makubexnet@gmail.com
 * @version 1.0.0 May 13, 2010
 * @since 1.0.0
 * 
 */
public class HistoryProcessInstanceProcedure extends BaseProcedure {

	/**
	 * @serialField
	 */
	private static final long serialVersionUID = -7927070725518590417L;

	public static void getListHistoryProcessInstance(Array processDefinitionId,
			Array processDefinitionKey, Array processDefinitionName,
			Array processInstanceKey, Array processInstanceId, Array state,
			Array outSystem, Date minStartTime, Date maxStartTime, int start,
			int limit, ResultSet[] results) {
		Connection conn = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_SELECT);
			sql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_ASTERISK);
			sql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_FROM);
			sql.append(VIEW_NAME_PROCESS_HIST_PINST);
			sql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_WHERE);
			sql.append("1 = 1 ");
			sql
					.append(generateCommonInStatement(
							IViewHistoryProcessInstanceProperties.VIEW_PROPERTY_PROCESS_DEFINITION_ID,
							(String[]) processDefinitionId.getArray()));
			sql
					.append(generateCommonInStatement(
							IViewHistoryProcessInstanceProperties.VIEW_PROPERTY_PROCESS_DEFINITION_KEY,
							(String[]) processDefinitionKey.getArray()));
			sql
					.append(generateCommonInStatement(
							IViewHistoryProcessInstanceProperties.VIEW_PROPERTY_PROCESS_DEFINITION_NAME,
							(String[]) processDefinitionName.getArray()));
			appendPageinationStatement(sql, start, limit);
			conn = getConnection();
			Statement stmt = conn.createStatement();
			results[0] = stmt.executeQuery(sql.toString());
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static String megrgeActivities(String processInstanceId, String state) {
		String result = "";
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		if (!"ended".equalsIgnoreCase(state)) {
			StringBuffer sql = new StringBuffer();
			sql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_SELECT);
			sql.append("activityname_");
			sql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_FROM);
			sql.append(TABLE_NAME_JBPM4_EXECUTION);
			sql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_WHERE);
			sql.append("INSTANCE_");
			sql.append(ProcessSystemDeclareKeys.EQUAL);
			sql.append(ProcessSystemDeclareKeys.SINGLE_QUOTES);
			sql.append(processInstanceId);
			sql.append(ProcessSystemDeclareKeys.SINGLE_QUOTES);
			sql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_OR);
			sql.append("PARENT_");
			sql.append(ProcessSystemDeclareKeys.EQUAL);
			sql.append(ProcessSystemDeclareKeys.SINGLE_QUOTES);
			sql.append(processInstanceId);
			sql.append(ProcessSystemDeclareKeys.SINGLE_QUOTES);
			try {
				conn = getConnection();
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sql.toString());
				while (rs.next()) {
					result += rs.getString(1);
					result += ProcessSystemDeclareKeys.COMMA;
				}
				if (result.length() > 0
						&& result.endsWith(ProcessSystemDeclareKeys.COMMA)) {
					result = result.substring(0, result.length() - 1);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if (rs != null)
						rs.close();
					if (stmt != null)
						stmt.close();
					if (conn != null)
						conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}

	public static void getListHistoryProcessInstance(Array processDefinitionId,
			Array processDefinitionKey, Array processDefinitionName,
			Array processInstanceKey, Array processInstanceId, Array state,
			Array outSystem, Date minStartTime, Date maxStartTime, int start,
			int limit, ResultSet[] results, Connection conn) {
		try {
			StringBuffer sql = new StringBuffer();
			sql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_SELECT);
			sql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_ASTERISK);
			sql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_FROM);
			sql.append(VIEW_NAME_PROCESS_HIST_PINST);
			sql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_WHERE);
			sql.append("1 = 1 ");
			sql
					.append(generateCommonInStatement(
							IViewHistoryProcessInstanceProperties.VIEW_PROPERTY_PROCESS_DEFINITION_ID,
							(String[]) processDefinitionId.getArray()));
			sql
					.append(generateCommonInStatement(
							IViewHistoryProcessInstanceProperties.VIEW_PROPERTY_PROCESS_DEFINITION_KEY,
							(String[]) processDefinitionKey.getArray()));
			sql
					.append(generateCommonInStatement(
							IViewHistoryProcessInstanceProperties.VIEW_PROPERTY_PROCESS_DEFINITION_NAME,
							(String[]) processDefinitionName.getArray()));
			appendPageinationStatement(sql, start, limit);
			System.out.println(sql.toString());
			Statement stmt = conn.createStatement();
			results[0] = stmt.executeQuery(sql.toString());
			while (results[0].next()) {
				System.out.println(results[0].getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
